using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Database
{
   public class BinhLuan
    {
        private int stt;

        public int Stt
        {
            get { return stt; }
            set { stt = value; }
        }
        private string hoten;

        public string Hoten
        {
            get { return hoten; }
            set { hoten = value; }
        }
        private string tieude;

        public string Tieude
        {
            get { return tieude; }
            set { tieude = value; }
        }
        private string noidung;

        public string Noidung
        {
            get { return noidung; }
            set { noidung = value; }
        }
        private DateTime ngaydang;

        public DateTime Ngaydang
        {
            get { return ngaydang; }
            set { ngaydang = value; }
        }
        private int mamay;

        public int Mamay
        {
            get { return mamay; }
            set { mamay = value; }
        }
       public BinhLuan()
       {
       }
       public BinhLuan(int stt, string hoten, string tieude, string noidung, DateTime ngaydang, int mamay)
       {
           this.stt = stt;
           this.hoten = hoten;
           this.tieude = tieude;
           this.noidung = noidung;
           this.ngaydang = ngaydang;
           this.mamay = mamay;
       }
       public static DataSet DSBL()
       {
           DataSet ds = new DataSet();
           string query = "select bl.stt,bl.hoten,bl.tieude,bl.noidung,bl.ngaydang,mt.tenmay from binhluan bl,maytinh mt where bl.mamay=mt.mamay";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query,db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public static DataSet DSBLTheoMaMay(int mamay)
       {
           DataSet ds = new DataSet();
           string query = "select bl.stt,bl.hoten,bl.tieude,bl.noidung,bl.ngaydang,mt.tenmay from binhluan bl,maytinh mt where bl.mamay=mt.mamay and mt.mamay="+mamay;
           ConnectToDatabase db = new ConnectToDatabase();
           SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
           sda.Fill(ds);
           return ds;
       }
       public bool ThemBL(string hoten, string tieude, string noidung, DateTime ngaydang, int mamay)
       {
           bool kq = true;

           try
           {
               string query = "insert into BinhLuan(hoten,tieude,noidung,ngaydang,mamay) values('{0}','{1}','{2}','{3}','{4}')";
               query = String.Format(query,hoten,tieude,noidung,ngaydang,mamay);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query,db.GetConnect());
               return cmd.ExecuteNonQuery() > 0;
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public bool XoaBL(int stt)
       {
           bool kq = true;

           try
           {
               string query = "Delete BinhLuan where stt='{0}'";
               query = String.Format(query,stt);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query, db.GetConnect());
               cmd.ExecuteNonQuery();
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public bool CapNhatBL(int stt,string hoten, string tieude, string noidung, DateTime ngaydang, int mamay)
       {
           bool kq = true;

           try
           {
               string query = "update BinhLuan set hoten='{0}',tieude='{1}',noidung='{2}',ngaydang='{3}',mamay='{4}' where stt='{5}'";
               query = String.Format(query, hoten, tieude, noidung, ngaydang, mamay,stt);
               ConnectToDatabase db = new ConnectToDatabase();
               SqlCommand cmd = new SqlCommand(query,db.GetConnect());
               return cmd.ExecuteNonQuery()>0;
           }
           catch
           {
               kq = false;
           }

           return kq;
       }
       public BinhLuan ThongTinBL(int stt)
       {
           string query = "select * from BinhLuan where stt=" + stt;
           ConnectToDatabase db = new ConnectToDatabase();
           SqlCommand sc = new SqlCommand(query, db.GetConnect());
           SqlDataReader reader = sc.ExecuteReader();
           BinhLuan bl = new BinhLuan();
           while (reader.Read())
           {
               bl.stt = reader.GetInt32(0);
               bl.hoten = reader.GetString(1);
               bl.tieude = reader.GetString(2);
               bl.noidung = reader.GetString(3);
               bl.ngaydang = reader.GetDateTime(4);
               bl.mamay = reader.GetInt32(5);
               
           }
           return bl;
       }
       public int LayTongBinhLuan(int mamay)
       {
           string query = " select count (*) from binhluan where mamay=" + mamay + "";
           ConnectToDatabase db = new ConnectToDatabase();
           SqlCommand cmd = new SqlCommand(query, db.GetConnect());
           int total = (int)cmd.ExecuteScalar();
           db.CloseConnect();
           return total;
       }
       public DataTable DanhSachBinhLuan(int mamay)
       {
           DataTable ds = new DataTable();
           try
           {
               string query = "select bl.stt,bl.hoten,bl.tieude,bl.noidung,bl.ngaydang,bl.mamay,kh.tenkh,mt.tenmay from binhluan bl,khachhang kh,maytinh mt where bl.hoten=kh.tenkh and bl.mamay=mt.mamay and mt.mamay=" + mamay + "";
               ConnectToDatabase db = new ConnectToDatabase();
               SqlDataAdapter sda = new SqlDataAdapter(query, db.GetConnect());
               sda.Fill(ds);
           }
           catch
           {
               ds = null;
           }
           return ds;
       }
       public void ThemBinhLuan(BinhLuan bl)
       {
           SqlCommand cmd;
           try
           {
               ConnectToDatabase db = new ConnectToDatabase();
               cmd = new SqlCommand("insert into BinhLuan(hoten,tieude,noidung,ngaydang,mamay) values(@hoten,@tieude,@noidung,@ngaydang,@mamay)", db.GetConnect());
               cmd.Parameters.Add("@hoten", SqlDbType.NVarChar).Value = bl.hoten;
               cmd.Parameters.Add("@tieude", SqlDbType.NVarChar).Value = bl.tieude;
               cmd.Parameters.Add("@noidung", SqlDbType.NVarChar).Value = bl.noidung;
               cmd.Parameters.Add("@ngaydang", SqlDbType.DateTime).Value = bl.ngaydang;
               cmd.Parameters.Add("@mamay", SqlDbType.Int).Value = bl.mamay;
               cmd.ExecuteNonQuery();

           }
           catch (Exception e)
           {
               Console.WriteLine(e.Message);
           }

       }
    }
}
